﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyHocPhi.Model;

namespace QuanLyHocPhi.BLL
{
    partial class BizSinhVien
    {
        /// <summary>
        /// Lấy sinh viên theo mã sinh viên
        /// </summary>
        /// <param name="maSinhVien">Mã sinh viên</param>
        /// <returns></returns>
        internal static SinhVien GetSinhVienByMSV(string maSinhVien)
        {
            using (var context = new QLHPEntities())
            {
                var sinhvien = (from sv in context.SinhVienDbSet.Include("LopGoc.LoaiHinhDaoTao")
                                where sv.MaSinhVien == maSinhVien
                                select sv).SingleOrDefault();
                return sinhvien;
            }
        }

        /// <summary>
        /// Lấy danh sách sinh viên, trong đó có tính tổng tiền đã nộp cho một học kỳ nào đó
        /// </summary>
        /// <param name="maLopGoc"></param>
        /// <param name="maHocKy"></param>
        /// <param name="laHocPhi"></param>
        /// <returns></returns>
        internal static List<CustomSinhVien> GetDanhSachSinhVien(string maLopGoc, string maHocKy, bool laHocPhi = true, string key = null)
        {
            using (var context = new QLHPEntities())
            {
                try
                {
                    var sinhvien = (from sv in context.SinhVienDbSet
                                    where sv.MaLopGoc == maLopGoc && (key == null || sv.Ten.Contains(key) || sv.HoDem.Contains(key) || sv.MaSinhVien.Contains(key))
                                    select new CustomSinhVien
                                    {
                                        DaChon = false,
                                        MaSinhVien = sv.MaSinhVien,
                                        HoDem = sv.HoDem,
                                        Ten = sv.Ten,
                                        Dien = sv.Dien,
                                        DoiTuong = sv.DoiTuong,
                                        NgaySinh = sv.NgaySinh,
                                        Giam = sv.Giam,
                                        SoTienDaNop = context.PhieuThuDbSet.Where
                                        (
                                            t =>
                                            (t.BienCheSinhVienVaoHocKy.MaSinhVien == sv.MaSinhVien) &&
                                            (t.BienCheSinhVienVaoHocKy.MaHocKy == maHocKy) &&
                                            (laHocPhi ? t.LaHocPhi == true : t.LaHocPhi == false)
                                        ).Sum(e => e.SoTien)
                                    });
                    return sinhvien.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

        internal static List<CustomSinhVien> GetDanhSachSinhVien(string maLoaiHinh,
            string namTS,
            string maLopGoc,
            string doiTuong,
            string dien,
            string maNamHoc,
            ref decimal tongLeHocPhi,
            ref decimal tongTienDaNop,
            bool laHocPhi = true,
            string key = null)
        {
            using (var context = new QLHPEntities())
            {
                try
                {
                    var sinhvien = (from sv in context.SinhVienDbSet
                                    where (
                                        (maLoaiHinh == "-1" ? true : sv.LopGoc.LoaiHinhDaoTao.MaLoaiHinh == maLoaiHinh) &&
                                        (namTS == "-1" ? true : sv.LopGoc.NamTuyenSinh == namTS) &&
                                        (dien == "-1" ? true : sv.Dien == dien) &&
                                        (doiTuong == "-1" ? true : sv.DoiTuong == doiTuong) &&
                                        (maLopGoc == "-1" ? true : sv.MaLopGoc == maLopGoc) &&
                                        (key == null || sv.Ten.Contains(key) || sv.HoDem.Contains(key) || sv.MaSinhVien.Contains(key))
                                        )
                                    select new CustomSinhVien
                                    {
                                        DaChon = false,
                                        MaSinhVien = sv.MaSinhVien,
                                        HoDem = sv.HoDem,
                                        Ten = sv.Ten,
                                        Dien = sv.Dien,
                                        DoiTuong = sv.DoiTuong,
                                        NgaySinh = sv.NgaySinh,
                                        Giam = sv.Giam,
                                        SoTienDaNop = context.PhieuThuDbSet.Where
                                        (
                                            t =>
                                            (t.BienCheSinhVienVaoHocKy.MaSinhVien == sv.MaSinhVien) &&
                                            (t.BienCheSinhVienVaoHocKy.HocKy.MaNamHoc == maNamHoc) &&
                                            (laHocPhi ? t.LaHocPhi == true : t.LaHocPhi == false)
                                        ).Sum(e => e.SoTien)
                                    });
                    if (laHocPhi)
                    {
                        tongLeHocPhi = (from e in context.BienCheSinhVienVaoHocKiDbSet
                                        where
                                            (
                                            (e.HocKy.MaNamHoc == maNamHoc) &&
                                            (maLoaiHinh == "-1" ? true : e.SinhVien.LopGoc.LoaiHinhDaoTao.MaLoaiHinh == maLoaiHinh) &&
                                            (namTS == "-1" ? true : e.SinhVien.LopGoc.NamTuyenSinh == namTS) &&
                                            (dien == "-1" ? true : e.SinhVien.Dien == dien) &&
                                            (doiTuong == "-1" ? true : e.SinhVien.DoiTuong == doiTuong) &&
                                            (maLopGoc == "-1" ? true : e.SinhVien.MaLopGoc == maLopGoc) &&
                                            (key == null || e.SinhVien.Ten.Contains(key) || e.SinhVien.HoDem.Contains(key) || e.SinhVien.MaSinhVien.Contains(key))
                                            )
                                        select (e.HocPhi - e.HocPhi * (e.Giam != null ? e.Giam : 0))).ToList().Sum();

                    }
                    else
                    {
                        tongLeHocPhi = (from e in context.BienCheSinhVienVaoHocKiDbSet
                                        where
                                             (
                                             (e.HocKy.MaNamHoc == maNamHoc) &&
                                             (maLoaiHinh == "-1" ? true : e.SinhVien.LopGoc.LoaiHinhDaoTao.MaLoaiHinh == maLoaiHinh) &&
                                             (namTS == "-1" ? true : e.SinhVien.LopGoc.NamTuyenSinh == namTS) &&
                                             (dien == "-1" ? true : e.SinhVien.Dien == dien) &&
                                             (doiTuong == "-1" ? true : e.SinhVien.DoiTuong == doiTuong) &&
                                             (maLopGoc == "-1" ? true : e.SinhVien.MaLopGoc == maLopGoc) &&
                                             (key == null || e.SinhVien.Ten.Contains(key) || e.SinhVien.HoDem.Contains(key) || e.SinhVien.MaSinhVien.Contains(key))
                                             )
                                        select (e.LePhi - e.LePhi * (e.Giam != null ? e.Giam : 0))).ToList().Sum();
                    }
                    tongTienDaNop = (from e in context.PhieuThuDbSet
                                     where
                                            (
                                            (e.LaHocPhi == laHocPhi) &&
                                            (e.BienCheSinhVienVaoHocKy.HocKy.MaNamHoc == maNamHoc) &&
                                            (maLoaiHinh == "-1" ? true : e.BienCheSinhVienVaoHocKy.SinhVien.LopGoc.LoaiHinhDaoTao.MaLoaiHinh == maLoaiHinh) &&
                                            (namTS == "-1" ? true : e.BienCheSinhVienVaoHocKy.SinhVien.LopGoc.NamTuyenSinh == namTS) &&
                                            (dien == "-1" ? true : e.BienCheSinhVienVaoHocKy.SinhVien.Dien == dien) &&
                                            (doiTuong == "-1" ? true : e.BienCheSinhVienVaoHocKy.SinhVien.DoiTuong == doiTuong) &&
                                            (maLopGoc == "-1" ? true : e.BienCheSinhVienVaoHocKy.SinhVien.MaLopGoc == maLopGoc) &&
                                            (key == null || e.BienCheSinhVienVaoHocKy.SinhVien.Ten.Contains(key) || e.BienCheSinhVienVaoHocKy.SinhVien.HoDem.Contains(key) || e.BienCheSinhVienVaoHocKy.SinhVien.MaSinhVien.Contains(key))
                                            )
                                     select (decimal)e.SoTien).ToList().Sum();
                    return sinhvien.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }


        internal static List<CustomSinhVien> GetDanhSachSinhVienTheoNam(string maLopGoc, string maNamHoc, bool laHocPhi = true, string ten = null)
        {
            using (var context = new QLHPEntities())
            {
                try
                {
                    var sinhvien = (from sv in context.SinhVienDbSet
                                    where sv.MaLopGoc == maLopGoc && (ten == null || sv.Ten.Contains(ten))
                                    select new CustomSinhVien
                                    {
                                        DaChon = false,
                                        MaSinhVien = sv.MaSinhVien,
                                        HoDem = sv.HoDem,
                                        Ten = sv.Ten,
                                        Dien = sv.Dien,
                                        DoiTuong = sv.DoiTuong,
                                        NgaySinh = sv.NgaySinh,
                                        SoTienDaNop = context.PhieuThuDbSet.Where
                                        (
                                            t =>
                                            (t.BienCheSinhVienVaoHocKy.MaSinhVien == sv.MaSinhVien) &&
                                            (t.BienCheSinhVienVaoHocKy.HocKy.MaNamHoc == maNamHoc) &&
                                            (laHocPhi ? t.LaHocPhi == true : t.LaHocPhi == false)
                                        ).ToList().Sum(e => e.SoTien),
                                        HocPhi = (int)context.BienCheSinhVienVaoHocKiDbSet.Where
                                        (
                                            t =>
                                            (t.MaSinhVien == sv.MaSinhVien) &&
                                            (t.HocKy.MaNamHoc == maNamHoc)
                                        ).ToList().Sum(e => e.HocPhi - e.HocPhi * e.Giam)
                                    });
                    return sinhvien.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }
        public static void GetTongTien(string maLopGoc, string maNamHoc, ref long tongLeHocPhi, ref long tongTienDaNop, bool laHocPhi = true)
        {
            using (var context = new QLHPEntities())
            {
                if (laHocPhi)
                {
                    tongLeHocPhi = (long)(from e in context.BienCheSinhVienVaoHocKiDbSet
                                          where e.HocKy.MaNamHoc == maNamHoc && e.SinhVien.MaLopGoc == maLopGoc
                                          select (e.HocPhi - e.HocPhi * (e.Giam != null ? e.Giam : 0))).ToList().Sum();

                }
                else
                {
                    tongLeHocPhi = (long)(from e in context.BienCheSinhVienVaoHocKiDbSet
                                          where e.HocKy.MaNamHoc == maNamHoc && e.SinhVien.MaLopGoc == maLopGoc
                                          select (e.LePhi - e.LePhi * (e.Giam != null ? e.Giam : 0))).ToList().Sum();
                }
                tongTienDaNop = (long)(from e in context.PhieuThuDbSet
                                       where e.LaHocPhi == laHocPhi && e.BienCheSinhVienVaoHocKy.HocKy.MaNamHoc == maNamHoc && e.BienCheSinhVienVaoHocKy.SinhVien.MaLopGoc == maLopGoc
                                       select e.SoTien).ToList().Sum();
            }
        }
        /// <summary>
        /// Lấy danh sách sinh viên của một lớp
        /// </summary>
        /// <param name="maLopGoc"></param>
        /// <returns></returns>
        internal static List<SinhVien> GetDanhSachSinhVien(string maLopGoc)
        {
            using (var context = new QLHPEntities())
            {
                try
                {
                    var sinhvien = from sv in context.SinhVienDbSet
                                   where sv.MaLopGoc == maLopGoc
                                   select sv;
                    return sinhvien.ToList();
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

    }
    [System.ComponentModel.DataAnnotations.Schema.NotMapped]
    class CustomSinhVien : SinhVien
    {
        public bool DaChon { get; set; }
        public int? SoTienDaNop { get; set; }
        public int? HocPhi { get; set; }
        public string SoTienDaNopStr
        {
            get { return SoTienDaNop.ToString().FormatCurrency(); }
        }
    }
}
